<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="${daoPackage}.${className}Dao">
    <resultMap id="BaseResultMap" type="${beanPackage}.${className}">
  	<#list primaryList as primaryOne>
    	<id column="${primaryOne.dbcname}" property="${primaryOne.proper}" jdbcType="${primaryOne.type}"/>
    </#list>
  	<#list normalList as normalOne>
	    <result column="${normalOne.dbcname}" property="${normalOne.proper}" jdbcType="${normalOne.type}"/>
    </#list>
    </resultMap>
    <sql id="Base_Column_List">
    ${columnFields}
    </sql>


    <sql id="Add_Column_List">
    ${addColumnFields}
    </sql>
    <select id="selectBy${key}" resultMap="BaseResultMap" parameterType="map">
        select
        <include refid="Base_Column_List"/>
    <#if yearTable>
    from ${tableName}${r"${year}"}
    <#else>
    from ${tableName}
    </#if>
        where ${key} = ${r"#{id}"}
    </select>
    <select id="getAutoGeneralID" resultType="java.util.Map">
        select max(${key}) as id
    <#if yearTable>
    from ${tableName}${r"${bridge.year}"}
    <#else>
    from ${tableName}
    </#if>
        where 1=1
    <#if parentId??>
        <choose>
            <when test="parentId != null and parentId != 'root' and parentId != 'null' and parentId != '' ">
                and ${parentId} = ${r"#{parentId}"}
            </when>
            <otherwise>
                and (${parentId} is null or ${parentId} = '' or parentid = '0')
            </otherwise>
        </choose>
    </#if>
    </select>
    <select id="getCount" resultType="java.util.Map">
        select count(1) as count
    <#if yearTable>
    from ${tableName}${r"${year}"}
    <#else>
    from ${tableName}
    </#if>
        where 1=1
        and companyid = ${r"#{companyid}"}
        <choose>
            <when test="keyword != null and keyword != '' and keyword != 'null' and keyword != '请输入关键字'">
                and (
              <#list keyWordFiltes as keyWordDB>
                  ${keyWordDB} like '${r"${keyword}"}%'
                  <#sep> or
              </#list>
                )
            </when>
        </choose>
    </select>

    <select id="getAll" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
    <#if yearTable>
    from ${tableName}${r"${year}"}
    <#else>
    from ${tableName}
    </#if>
        where 1=1 and companyid = ${r"#{companyid}"}
    </select>

    <!--如果不是带年份的表，需要自己加入年份过滤，这里不好知道年份字段的数据库名称 -->
    <select id="validator" resultType="java.util.Map">
        select
        <include refid="Base_Column_List"/>
    <#if yearTable>
    from ${tableName}${r"${bridge.year}"}
    <#else>
    from ${tableName}
    </#if>
        where 1=1 and ${r"${key}"} = ${r"#{value}"} and companyid = ${r"#{bridge.companyid}"}
    </select>


    <select id="get" resultMap="BaseResultMap" parameterType="map">
        select
        <include refid="Base_Column_List"/>
    <#if yearTable>
    from ${tableName}${r"${year}"}
    <#else>
    from ${tableName}
    </#if>
        where 1=1 and companyid = ${r"#{companyid}"}
        <choose>
            <when test="keyword != null and keyword != '' and keyword != 'null' and keyword != '请输入关键字'">
            <#if leaf??>and ${leaf} = 1</#if>
                and (
              <#list keyWordFiltes as keyWordDB>
                  ${keyWordDB} like '${r"${keyword}"}%'
                  <#sep> or
              </#list>
                )
            </when>
            <otherwise>
            <#if parentId??>
                <choose>
                    <when test="node != null and node != 'root' and node != 'null' and node != '' ">
                        and ${parentId} = ${r"#{node}"}
                    </when>
                    <otherwise>
                        and (${parentId} is null or ${parentId} = '' or ${parentId} = '0')
                    </otherwise>
                </choose>
            </#if>
            </otherwise>
        </choose>
        order by ${key}
    </select>

    <select id="getPage" resultMap="BaseResultMap" parameterType="map">
        select
        <include refid="Base_Column_List"/>
        from
        (
            select
            <include refid="Base_Column_List"/>
            ,row_number() over
            (
            order by
             <#list primaryList as one>
                 ${one.dbcname}<#sep>,
             </#list> asc
            ) n
           <#if yearTable>
           from ${tableName}${r"${year}"}
           <#else>
           from ${tableName}
           </#if>
            where 1=1
            and companyid = ${r"#{companyid}"}
            <choose>
                <when test="keyword != null and keyword != '' and keyword != 'null' and keyword != '请输入关键字'">
                    and (
                      <#list keyWordFiltes as keyWordDB>
                          ${keyWordDB} like '${r"${keyword}"}%'
                          <#sep> or
                      </#list>
                    )
                </when>
            </choose>
        ) a
        where a.n &gt; ${r"#{start}"}
        and a.n &lt;= ${r"#{end}"}
    </select>

    <insert id="insert" parameterType="map">
        insert into
  		<#if yearTable>
            ${tableName}${r"${year}"}
        <#else>
            ${tableName}
        </#if>
        (
        <include refid="Add_Column_List"/>
        )
        values(${addValueFields})
    </insert>

    <update id="update" parameterType="map">
        update
  		<#if yearTable>
            ${tableName}${r"${year}"}
        <#else>
            ${tableName}
        </#if>
        set
			<#list updateList as update>
                ${update.dbcname} = ${r"#{"}${update.proper}}<#sep>,
            </#list>
        where ${key} = ${r"#{whereId}"}
    </update>
<#if leaf??>
    <update id="changeLeaf" parameterType="map">
        update
  		<#if yearTable>
            ${tableName}${r"${year}"}
        <#else>
            ${tableName}
        </#if>
        set ${leaf} = ${r"#{value}"}
        where ${key} = ${r"#{id}"}
    </update>
</#if>
    <delete id="deleteByPrimaryKey" parameterType="map">
        delete from
	    <#if yearTable>
            ${tableName}${r"${year}"}
        <#else>
            ${tableName}
        </#if>
        where ${key} = ${r"#{id}"}
        <#if parentId??>
            or ${parentId} = ${r"#{id}"}
        </#if>
    </delete>
    <delete id="deleteAll">
        <#if yearTable>
            delete from ${tableName}${r"${year}"} where 1=1
        <#else>
          delete from ${tableName} where 1=1
        </#if>

    </delete>
  <#if copyLastYearData == true>

  <insert id="copyLastYear">
      <#if yearTable>
          insert into ${tableName}${r"${year}"}
      <#else>
          insert into ${tableName}
      </#if>
      (
      <include refid="Add_Column_List"/>)
      select
      <include refid="Add_Column_List"/> from
      ${tableName}${r"${lastyear}"} where 1=1
  </insert>
  </#if>
  <#if importData == true>
  <insert id="insert${className}s">
      insert into
      <#if yearTable>
          ${tableName}${r"${year}"}
      <#else>
          ${tableName}
      </#if>
        (
        <include refid="Add_Column_List"/>
        )
        values
      [<![CDATA[
      <foreach collection="list" item="single" index="index" separator=",">
      ]]>
          (${addValueFieldsWithSingle})
      ${r"</foreach>"}
  </insert>
  </#if>
</mapper>